Parameter Metadata Support

The JDBC driver provides parameter metadata support for databases that provide native support for parameter metadata. For databases that do not provide native parameter metadata support, such as Oracle or Informix, the JDBC driver supports parameter metadata for valid ANSI SQL SELECT statements.

INSERT and UPDATE Statements

The JDBC driver supports returning parameter metadata for all types of SQL statements for databases that provide native support for parameter metadata.

For databases that do not provide native parameter metadata support, the JDBC driver supports returning parameter metadata for the following forms of Insert and Update statements:

INSERT INTO foo VALUES (?, ?, ?)
INSERT INTO foo (col1, col2, col3) VALUES (?, ?, ?)
UPDATE foo SET col1=?, col2=?, col3=? WHERE col1 operator ? [{AND | OR} col2 operator ?]

where operator is any of the following SQL operators: =, <, >, <=, >=, and <>.

Select Statements

The JDBC driver supports returning parameter metadata for Select statements that contain parameters in ANSI SQL 92 entry-level predicates, for example, such as COMPARISON, BETWEEN, IN, LIKE, and EXISTS predicate constructs. Refer to the ANSI SQL reference for detailed syntax.

Parameter metadata can be returned for a Select statement if one of the following conditions is true:

The statement contains a predicate value expression that can be targeted against the source tables in the associated FROM clause. For example:

SELECT * FROM foo WHERE bar > ?

In this case, the value expression "bar" can be targeted against the table "foo" to determine the appropriate metadata for the parameter.

The statement contains a predicate value expression part that is a nested query. The nested query's metadata must describe a single column. For example:

SELECT * FROM foo WHERE (SELECT x FROM y
WHERE z = 1) < ?

The following Select statements show further examples for which parameter metadata can be returned:

SELECT col1, col2 FROM foo WHERE col1 = ? and col2 > ? 
SELECT ... WHERE colname = (SELECT col2 FROM t2  
   WHERE col3 = ?) 
SELECT ... WHERE colname LIKE ? 
SELECT ... WHERE colname BETWEEN ? and ? 
SELECT ... WHERE colname IN (?, ?, ?) 
SELECT ... WHERE EXISTS(SELECT ... FROM T2 WHERE col1 < ?) 

ANSI SQL 92 entry-level predicates in a WHERE clause containing GROUP BY, HAVING, or ORDER BY statements are supported. For example:

SELECT * FROM t1 WHERE col = ? ORDER BY 1 

Joins are supported. For example:

SELECT * FROM t1,t2 WHERE t1.col1 = ? 

Fully qualified names and aliases are supported. For example:

SELECT a, b, c, d FROM T1 AS A, T2 AS B WHERE A.a = ? 
   and B.b = ?"